﻿// -----------------------------------------------------------------------
// <copyright file="MalevichDal.cs" company="Microsoft IT">
//     Copyright (c) Microsoft Corporation 2011. All rights reserved.
// </copyright>
// -----------------------------------------------------------------------

namespace Internal.TeamFoundationServer.Submit
{
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using System.Diagnostics.CodeAnalysis;
    using System.Globalization;
    using System.IO;
    using System.Text;
    using System.Windows.Forms;
    using Properties;

    /// <summary>
    /// Malevich Data Access Layer.
    /// </summary>
    internal static class MalevichDal
    {
        /// <summary>
        /// Needs Work Constant.
        /// </summary>
        public const string NeedsWorkConst = "Needs work";

        /// <summary>
        /// SQL connection string.
        /// </summary>
        private const string SqlConnectionStringConst = "Server={0};Database=CodeReview;Integrated Security=True";

        /// <summary>
        /// Query to get ChangeList ID.
        /// </summary>
        private const string GetChangeListIdConst = "SELECT ID FROM ChangeList WHERE (UserName = N'{0}') AND (CL = N'{1}') AND (UserClient = N'{2}')";

        /// <summary>
        /// Query to get ChangeList Stage.
        /// </summary>
        private const string GetChangeListStageConst = "SELECT Stage FROM ChangeList WHERE (Id = N'{0}')";

        /// <summary>
        /// Query to check if anyone provided feedback.
        /// </summary>
        private const string GetReviewFeedbackConst = "SELECT ID FROM Review WHERE (ChangeListId = {0})";

        /// <summary>
        /// Query to get the list of reviewers.
        /// </summary>
        private const string GetInvitedReviewersConst = "SELECT ReviewerAlias FROM Reviewer WHERE (ChangeListId = {0})";

        /// <summary>
        /// Query to get the list of signed off reviewers.
        /// </summary>
        private const string GetSignedOffReviewersConst = "SELECT UserName, OverallStatus FROM Review WHERE (ChangeListId = {0}) ORDER BY TimeStamp DESC";

        /// <summary>
        /// Get ChangeList ID
        /// </summary>
        /// <param name="changeListName">Change List name.</param>
        /// <param name="workspaceName">Workspace Name.</param>
        /// <returns>If found, Change list ID, otherwise returns -1.</returns>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We should no change Malevich database code to not break future compatibility.")]
        internal static int GetChangeListId(string changeListName, string workspaceName)
        {
            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                string query = string.Format(CultureInfo.InvariantCulture, GetChangeListIdConst, Environment.UserName, changeListName, workspaceName);
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    object obj = sqlCommand.ExecuteScalar();
                    return obj == null ? -1 : (int)obj;
                }
            }
        }

        /// <summary>
        /// Get ChangeList Stage
        /// </summary>
        /// <param name="changeListId">Change List Id.</param>
        /// <returns>Return Change list stage.</returns>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We should no change Malevich database code to not break future compatibility.")]
        internal static int GetChangeListStage(int changeListId)
        {
            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                string query = string.Format(CultureInfo.InvariantCulture, GetChangeListStageConst, changeListId);
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    object obj = sqlCommand.ExecuteScalar();
                    return obj == null ? -1 : (int)obj;
                }
            }
        }

        /// <summary>
        /// Verify if review has any feedback.
        /// </summary>
        /// <param name="changeListId">Change list ID.</param>
        /// <returns>True if anyone start reviewing, otherwise false.</returns>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We should no change Malevich database code to not break future compatibility.")]
        internal static bool ReviewHasFeedback(int changeListId)
        {
            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                string query = string.Format(CultureInfo.InvariantCulture, GetReviewFeedbackConst, changeListId);
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    object obj = sqlCommand.ExecuteScalar();
                    return obj != null;
                }
            }
        }

        /// <summary>
        /// Get reviewers.
        /// </summary>
        /// <param name="changeListId">Change list ID.</param>
        /// <returns>List with reviewers names.</returns>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We should no change Malevich database code to not break future compatibility.")]
        internal static string GetInvitedReviewers(int changeListId)
        {
            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                string query = string.Format(CultureInfo.InvariantCulture, GetInvitedReviewersConst, changeListId);
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    using (var sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        var stringBuilder = new StringBuilder();

                        while (sqlDataReader.Read())
                        {
                            stringBuilder.Append(sqlDataReader[0] + "; ");
                        }

                        return stringBuilder.ToString();
                    }
                }
            }
        }

        /// <summary>
        /// Get signed off reviewers.
        /// </summary>
        /// <param name="changeListName">Change list name.</param>
        /// <param name="workspaceName">Workspace Name.</param>
        /// <returns>List with reviewers names.</returns>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We should no change Malevich database code to not break future compatibility.")]
        internal static string GetSignedOffReviewers(string changeListName, string workspaceName)
        {
            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                var changeListId = GetChangeListId(changeListName, workspaceName);
                var query = string.Format(CultureInfo.InvariantCulture, GetSignedOffReviewersConst, changeListId);
                using (var sqlCommand = new SqlCommand(query, sqlConnection))
                {
                    // First get the list of people who provide comments.
                    var signOffReviewers = new Dictionary<string, int>();
                    using (var sqlDataReader = sqlCommand.ExecuteReader())
                    {
                        var stringBuilder = new StringBuilder();

                        // Add the list of people who provided comments in the dictionary.
                        while (sqlDataReader.Read())
                        {
                            var name = sqlDataReader[0].ToString();
                            if (name == Environment.UserName)
                            {
                                continue;
                            }

                            if (!signOffReviewers.ContainsKey(name))
                            {
                                signOffReviewers.Add(name, int.Parse(sqlDataReader[1].ToString(), CultureInfo.InvariantCulture));
                            }
                        }

                        // Now read the dictionary and get only the list of people who provide LGTM feedback.
                        foreach (var name in signOffReviewers)
                        {
                            if (name.Value == 1 || name.Value == 2)
                            {
                                if (stringBuilder.Length > 0)
                                {
                                    stringBuilder.Append("; ");
                                }

                                stringBuilder.Append(name.Key);
                            }
                            else if (name.Value == 0)
                            {
                                // If anyone provided feedback that needs work, user cannot check-in code.
                                return string.Format(CultureInfo.InvariantCulture, "{0}: {1}", NeedsWorkConst, name.Key);
                            }
                        }

                        return stringBuilder.ToString();
                    }
                }
            }
        }

        /// <summary>
        /// Grant Vote Permission.
        /// </summary>
        /// <param name="changeListName">Change List name.</param>
        /// <param name="workspaceName">Workspace Name.</param>
        /// <param name="alias">User alias.</param>
        [SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities", Justification = "We are passing a stored procedure command type and not a SQL query. we can ignore this warning.")]
        internal static void GrantVotePermission(string changeListName, string workspaceName, string alias)
        {
            int changeListId = GetChangeListId(changeListName, workspaceName);

            using (var sqlConnection = GetSqlConnection())
            {
                if (sqlConnection.State == ConnectionState.Closed)
                {
                    sqlConnection.Open();
                }

                using (var sqlCommand = new SqlCommand())
                {
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = TfsSubmitConfig.GrantVotePermissionProcName;
                    sqlCommand.Connection = sqlConnection;

                    sqlCommand.Parameters.Add("@ReviewerAlias", SqlDbType.NVarChar, 50);
                    sqlCommand.Parameters["@ReviewerAlias"].Direction = ParameterDirection.Input;
                    sqlCommand.Parameters["@ReviewerAlias"].Value = alias.Trim();

                    sqlCommand.Parameters.Add("@ChangeListId", SqlDbType.Int, 4);
                    sqlCommand.Parameters["@ChangeListId"].Direction = ParameterDirection.Input;
                    sqlCommand.Parameters["@ChangeListId"].Value = changeListId;

                    sqlCommand.Parameters.Add("@result", SqlDbType.Int, 4);
                    sqlCommand.Parameters["@result"].Direction = ParameterDirection.InputOutput;
                    sqlCommand.Parameters["@result"].Value = 0;

                    try
                    {
                        sqlCommand.ExecuteNonQuery();
                    }
                    catch (SqlException err)
                    {
                        MessageBox.Show(
                            err.Message,
                            Resources.ErrorString,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error,
                            MessageBoxDefaultButton.Button1,
                            MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);
                    }
                }
            }
        }

        /// <summary>
        /// Open Code review in web browser.
        /// </summary>
        /// <param name="changeListName">Change List Name.</param>
        /// <param name="workspaceName">Workspace Name.</param>
        internal static void OpenCodeReviewInBrowser(string changeListName, string workspaceName)
        {
            int changeListId = GetChangeListId(changeListName, workspaceName);
            OpenCodeReviewInBrowser(changeListId);
        }

        /// <summary>
        /// Open Code review in web browser.
        /// </summary>
        /// <param name="changeListId">Change List Id.</param>
        internal static void OpenCodeReviewInBrowser(int changeListId)
        {
            // Open Code Review
            if (changeListId <= 0)
            {
                return;
            }

            using (var process = new Process())
            {
                const string MalevichUrl = "http://{0}/Malevich/default.aspx?cid={1}";
                process.StartInfo.FileName = string.Format(CultureInfo.InvariantCulture, MalevichUrl, TfsSubmitConfig.MalevichServerName, changeListId);

                process.Start();
            }
        }

        /// <summary>
        /// Close Code Review.
        /// </summary>
        /// <param name="changeListName">Change List Name.</param>
        /// <returns>Return operation status.</returns>
        internal static bool CloseCodeReview(string changeListName)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;

                string arguments = string.Format(
                    CultureInfo.InvariantCulture,
                    "close \"{0}\" --database \"{1}\" --force", // We don't care if there is still open comments, so we force to close.
                    changeListName,
                    TfsSubmitConfig.MalevichServerName);

                var output = string.Empty;

                // Send code review.
                using (var process = new Process())
                {
                    if (!File.Exists(TfsSubmitConfig.MalevichTool))
                    {
                        MessageBox.Show(
                            Resources.CouldNotFindCodeReviewApp + Environment.NewLine + TfsSubmitConfig.MalevichTool,
                            Resources.ErrorString,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error,
                            MessageBoxDefaultButton.Button1,
                            MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                        return false;
                    }

                    process.StartInfo.FileName = TfsSubmitConfig.MalevichTool;
                    process.StartInfo.Arguments = arguments;
                    process.StartInfo.UseShellExecute = false;
                    process.StartInfo.RedirectStandardOutput = true;
                    process.StartInfo.CreateNoWindow = true;
                    process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;

                    if (process.Start())
                    {
                        output = process.StandardOutput.ReadToEnd();
                        process.WaitForExit();

                        string[] lines = output.Split('\n');
                        for (int i = lines.Length - 1; i >= 0; i--)
                        {
                            if (lines[i].Trim().Length > 0 &&
                                lines[i].Trim().Contains(string.Format(CultureInfo.InvariantCulture, "{0} closed.", changeListName)))
                            {
                                MessageBox.Show(
                                    string.Format(CultureInfo.InvariantCulture, Resources.ReviewClosedString, changeListName),
                                    Resources.InformationString,
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information,
                                    MessageBoxDefaultButton.Button1,
                                    MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                                return true;
                            }
                        }
                    }
                }

                MessageBox.Show(
                    Resources.UnabledToCloseCodeReviewString + Environment.NewLine + output,
                    Resources.ErrorString,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error,
                    MessageBoxDefaultButton.Button1,
                    MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                return false;
            }
            finally
            {
                Cursor.Current = Cursors.Default;
            }
        }

        /// <summary>
        /// Activate Code Review.
        /// </summary>
        /// <param name="changeListName">Change List Name.</param>
        /// <returns>Return operation status.</returns>
        internal static bool ActivateCodeReview(string changeListName)
        {
            try
            {
                Cursor.Current = Cursors.WaitCursor;

                string arguments = string.Format(
                    CultureInfo.InvariantCulture,
                    "reopen \"{0}\" --database \"{1}\"",
                    changeListName,
                    TfsSubmitConfig.MalevichServerName);

                string output = string.Empty;

                // Send code review.
                using (var process = new Process())
                {
                    if (!File.Exists(TfsSubmitConfig.MalevichTool))
                    {
                        MessageBox.Show(
                            Resources.CouldNotFindCodeReviewApp + Environment.NewLine + TfsSubmitConfig.MalevichTool,
                            Resources.ErrorString,
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Error,
                            MessageBoxDefaultButton.Button1,
                            MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                        return false;
                    }

                    process.StartInfo.FileName = TfsSubmitConfig.MalevichTool;
                    process.StartInfo.Arguments = arguments;
                    process.StartInfo.UseShellExecute = false;
                    process.StartInfo.RedirectStandardOutput = true;
                    process.StartInfo.CreateNoWindow = true;
                    process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;

                    if (process.Start())
                    {
                        output = process.StandardOutput.ReadToEnd();
                        process.WaitForExit();

                        string[] lines = output.Split('\n');
                        for (int i = lines.Length - 1; i >= 0; i--)
                        {
                            if (lines[i].Trim().Length > 0 &&
                                lines[i].Trim().Contains(string.Format(CultureInfo.InvariantCulture, "{0} reopened.", changeListName)))
                            {
                                MessageBox.Show(
                                    string.Format(CultureInfo.InvariantCulture, Resources.ReviewActivatedString, changeListName),
                                    Resources.InformationString,
                                    MessageBoxButtons.OK,
                                    MessageBoxIcon.Information,
                                    MessageBoxDefaultButton.Button1,
                                    MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                                return true;
                            }
                        }
                    }
                }

                MessageBox.Show(
                    Resources.UnableToActivateCodeReviewString + Environment.NewLine + output,
                    Resources.ErrorString,
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Error,
                    MessageBoxDefaultButton.Button1,
                    MessageBoxOptions.RightAlign & MessageBoxOptions.RtlReading);

                return false;
            }
            finally
            {
                Cursor.Current = Cursors.Default;
            }
        }

        /// <summary>
        /// Get SQL connection.
        /// </summary>
        /// <returns>SQL connection object.</returns>
        private static SqlConnection GetSqlConnection()
        {
            string connectionString = string.Format(CultureInfo.InvariantCulture, SqlConnectionStringConst, TfsSubmitConfig.MalevichServerName);
            return new SqlConnection(connectionString);
        }
    }
}
